<?php /* 	These code was generated using phpCIGen v 0.1.a (21/04/2009)
	#zaqi 		zaqi.smart@gmail.com,http://zenzaqi.blogspot.com,
    #songbee	mukhlisona@gmail.com
	#CV. Trust Solution, jl. Saronojiwo 19 Surabaya, http://www.ts.co.id

	+ Module  		: t_bill Model
	+ Description	: For record model process back-end
	+ Filename 		: c_t_bill.php
 	+ creator 		: 
 	+ Created on 06/Mar/2012 12:35:49

*/

class M_t_bill extends CI_Model{

		//constructor
		function M_t_bill() {
			parent::__construct();
		}

		//function for get list record
		function t_bill_list($filter,$start,$end){
			$sql = "SELECT  t_bill.bill_id,
                                            t_order.order_code,
                                            t_bill.bill_date,
                                            t_bill.bill_no,
                                            t_bill.bill_value,
                                            t_bill.bill_note
                                    FROM
                                            t_bill
                                    LEFT JOIN t_order ON t_order.order_id = t_bill.t_order_id";

			// For simple search
			if ($filter<>""){
				$sql .=eregi("WHERE",$sql)? " AND ":" WHERE ";
				$sql .= " (lower(bill_id) LIKE '%".addslashes(strtolower($filter))."%' OR
							lower(t_order_id) LIKE '%".addslashes(strtolower($filter))."%' OR
							lower(bill_date) LIKE '%".addslashes(strtolower($filter))."%' OR
							lower(bill_no) LIKE '%".addslashes(strtolower($filter))."%' OR
							lower(bill_value) LIKE '%".addslashes(strtolower($filter))."%' OR
							lower(bill_note) LIKE '%".addslashes(strtolower($filter))."%' OR
							lower(bill_stat) LIKE '%".addslashes(strtolower($filter))."%' )";
			}

			$result = $this->db->query($sql);
			$nbrows = $result->num_rows();
                        $limit = $sql." LIMIT ".$start.",".$end;
			$result = $this->db->query($limit);

			if($nbrows>0){
				foreach($result->result() as $row){
					$arr[] = $row;
				}
				$jsonresult = json_encode($arr);
				return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
			} else {
				return '({"total":"0", "results":""})';
			}
		}

		//function for create new record
		function t_bill_create($t_order_id,
				 $bill_date,
				 $bill_no,
				 $bill_value,
				 $bill_note,
				 $bill_stat){
			$data = array(
						 "t_order_id"=>$t_order_id,						 "bill_date"=>$bill_date,						 "bill_no"=>$bill_no,						 "bill_value"=>$bill_value,						 "bill_note"=>$bill_note,						 "bill_stat"=>$bill_stat					);
			$this->db->insert('t_bill', $data);
			if($this->db->affected_rows())
				return '1';
			else
				return '0';
		}

		//function for update record
		function t_bill_update($bill_id,
				 $t_order_id,
				 $bill_date,
				 $bill_no,
				 $bill_value,
				 $bill_note,
				 $bill_stat){
			$data = array(
						"t_order_id"=>$t_order_id,						"bill_date"=>$bill_date,						"bill_no"=>$bill_no,						"bill_value"=>$bill_value,						"bill_note"=>$bill_note,						"bill_stat"=>$bill_stat					);

			$this->db->where('bill_id', $bill_id);
			$this->db->update('t_bill', $data);
			return '1';
		}

		//fcuntion for delete record
		function t_bill_delete($pkid){
			// You could do some checkups here and return '0' or other error consts.
			// Make a single query to delete all of the t_bills at the same time :
			if(sizeof($pkid)<1){
				return '0';
			} else if (sizeof($pkid) == 1){
				$sql = "DELETE FROM t_bill WHERE bill_id = ".$pkid[0];
				$this->db->query($sql);
			} else {
				$sql = "DELETE FROM t_bill WHERE ";
				for($i = 0; $i < sizeof($pkid); $i++){
					$sql = $sql . "bill_id= ".$pkid[$i];
					if($i<sizeof($pkid)-1){
						$sql = $sql . " OR ";
					}
				}
				$this->db->query($sql);
			}
			if($this->db->affected_rows()>0)
				return '1';
			else
				return '0';
		}

		//function for advanced search record
		function t_bill_search($bill_id,
				 $t_order_id,
				 $bill_date,
				 $bill_no,
				 $bill_value,
				 $bill_note,
				 $bill_stat,$start,$end){
			//full query
			$sql = "SELECT bill_id,
							 t_order_id,
							 bill_date,
							 bill_no,
							 bill_value,
							 bill_note,
							 bill_stat					  FROM 	 t_bill";

			if($bill_id!=''){
				$sql.=eregi("WHERE",$sql)?" AND ":" WHERE ";
				$sql.= " lower(bill_id) LIKE '%".addslashes(strtolower($bill_id))."%'";
			};
			if($t_order_id!=''){
				$sql.=eregi("WHERE",$sql)?" AND ":" WHERE ";
				$sql.= " lower(t_order_id) LIKE '%".addslashes(strtolower($t_order_id))."%'";
			};
			if($bill_date!=''){
				$sql.=eregi("WHERE",$sql)?" AND ":" WHERE ";
				$sql.= " lower(bill_date) LIKE '%".addslashes(strtolower($bill_date))."%'";
			};
			if($bill_no!=''){
				$sql.=eregi("WHERE",$sql)?" AND ":" WHERE ";
				$sql.= " lower(bill_no) LIKE '%".addslashes(strtolower($bill_no))."%'";
			};
			if($bill_value!=''){
				$sql.=eregi("WHERE",$sql)?" AND ":" WHERE ";
				$sql.= " lower(bill_value) LIKE '%".addslashes(strtolower($bill_value))."%'";
			};
			if($bill_note!=''){
				$sql.=eregi("WHERE",$sql)?" AND ":" WHERE ";
				$sql.= " lower(bill_note) LIKE '%".addslashes(strtolower($bill_note))."%'";
			};
			if($bill_stat!=''){
				$sql.=eregi("WHERE",$sql)?" AND ":" WHERE ";
				$sql.= " lower(bill_stat) LIKE '%".addslashes(strtolower($bill_stat))."%'";
			};

			$result = $this->db->query($sql);
			$nbrows = $result->num_rows();
                        $limit = $sql." LIMIT ".$start.",".$end;
			$result = $this->db->query($limit);

			if($nbrows>0){
				foreach($result->result() as $row){
					$arr[] = $row;
				}
				$jsonresult = json_encode($arr);
				return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
			} else {
				return '({"total":"0", "results":""})';
			}
		}

		//function for print record
		function t_bill_print($bill_id,
				 $t_order_id,
				 $bill_date,
				 $bill_no,
				 $bill_value,
				 $bill_note,
				 $bill_stat,$option,$filter){
			//full query
			$sql = "SELECT bill_id,
							 t_order_id,
							 bill_date,
							 bill_no,
							 bill_value,
							 bill_note,
							 bill_stat					  FROM 	 t_bill";
			if($option=='LIST'){
				$sql .=eregi("WHERE",$sql)? " AND ":" WHERE ";
				$sql .= " (lower(bill_id) LIKE '%".addslashes(strtoloer($filter))."%' OR lower(t_order_id) LIKE '%".addslashes(strtoloer($filter))."%' OR lower(bill_date) LIKE '%".addslashes(strtoloer($filter))."%' OR lower(bill_no) LIKE '%".addslashes(strtoloer($filter))."%' OR lower(bill_value) LIKE '%".addslashes(strtoloer($filter))."%' OR lower(bill_note) LIKE '%".addslashes(strtoloer($filter))."%' OR lower(bill_stat) LIKE '%".addslashes(strtoloer($filter))."%' )";
			} else if($option=='SEARCH'){
				if($bill_id!=''){
					$sql.=eregi("WHERE",$sql)?" AND ":" WHERE ";
					$sql.= " lower(bill_id) LIKE '%".addslashes(strtolower($bill_id))."%'";
				};
				if($t_order_id!=''){
					$sql.=eregi("WHERE",$sql)?" AND ":" WHERE ";
					$sql.= " lower(t_order_id) LIKE '%".addslashes(strtolower($t_order_id))."%'";
				};
				if($bill_date!=''){
					$sql.=eregi("WHERE",$sql)?" AND ":" WHERE ";
					$sql.= " lower(bill_date) LIKE '%".addslashes(strtolower($bill_date))."%'";
				};
				if($bill_no!=''){
					$sql.=eregi("WHERE",$sql)?" AND ":" WHERE ";
					$sql.= " lower(bill_no) LIKE '%".addslashes(strtolower($bill_no))."%'";
				};
				if($bill_value!=''){
					$sql.=eregi("WHERE",$sql)?" AND ":" WHERE ";
					$sql.= " lower(bill_value) LIKE '%".addslashes(strtolower($bill_value))."%'";
				};
				if($bill_note!=''){
					$sql.=eregi("WHERE",$sql)?" AND ":" WHERE ";
					$sql.= " lower(bill_note) LIKE '%".addslashes(strtolower($bill_note))."%'";
				};
				if($bill_stat!=''){
					$sql.=eregi("WHERE",$sql)?" AND ":" WHERE ";
					$sql.= " lower(bill_stat) LIKE '%".addslashes(strtolower($bill_stat))."%'";
				};
			}
			$query = $this->db->query($sql);
			return $query->result();
		}

		//function  for export to excel
		function t_bill_export_excel($bill_id ,$t_order_id ,$bill_date ,$bill_no ,$bill_value ,$bill_note ,$bill_stat ,$option,$filter){
			//full query
			$sql = "SELECT bill_id,
							 t_order_id,
							 bill_date,
							 bill_no,
							 bill_value,
							 bill_note,
							 bill_stat					  FROM 	 t_bill";
			if($option=='LIST'){
				$sql .=eregi("WHERE",$sql)? " AND ":" WHERE ";
				$sql .= "(lower(bill_id) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(t_order_id) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(bill_date) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(bill_no) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(bill_value) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(bill_note) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(bill_stat) LIKE '%".addslashes(strtolower($filter))."%' )";
			} else if($option=='SEARCH'){
				if($bill_id!=''){
					$sql.=eregi("WHERE",$sql)?" AND ":" WHERE ";
					$sql.= " lower(bill_id) LIKE '%".addslashes(strtolower($bill_id))."%'";
				};
				if($t_order_id!=''){
					$sql.=eregi("WHERE",$sql)?" AND ":" WHERE ";
					$sql.= " lower(t_order_id) LIKE '%".addslashes(strtolower($t_order_id))."%'";
				};
				if($bill_date!=''){
					$sql.=eregi("WHERE",$sql)?" AND ":" WHERE ";
					$sql.= " lower(bill_date) LIKE '%".addslashes(strtolower($bill_date))."%'";
				};
				if($bill_no!=''){
					$sql.=eregi("WHERE",$sql)?" AND ":" WHERE ";
					$sql.= " lower(bill_no) LIKE '%".addslashes(strtolower($bill_no))."%'";
				};
				if($bill_value!=''){
					$sql.=eregi("WHERE",$sql)?" AND ":" WHERE ";
					$sql.= " lower(bill_value) LIKE '%".addslashes(strtolower($bill_value))."%'";
				};
				if($bill_note!=''){
					$sql.=eregi("WHERE",$sql)?" AND ":" WHERE ";
					$sql.= " lower(bill_note) LIKE '%".addslashes(strtolower($bill_note))."%'";
				};
				if($bill_stat!=''){
					$sql.=eregi("WHERE",$sql)?" AND ":" WHERE ";
					$sql.= " lower(bill_stat) LIKE '%".addslashes(strtolower($bill_stat))."%'";
				};
			}
			$query = $this->db->query($sql);
			return $sql;
		}
		
		function cetak($bill_id){
			$sql="SELECT t_bill.bill_no,
					t_order.m_customer_id,
					m_customer.customer_name,
					m_customer.customer_address,
					t_bill.bill_value,
					t_order.order_note,
					t_bill.bill_date,
					t_orderdet.orderdet_qty,
					t_orderdet.orderdet_detail,
					t_orderdet.orderdet_price,
					(t_orderdet.orderdet_qty * t_orderdet.orderdet_price) AS jumlah
				FROM t_orderdet
					JOIN t_order ON(t_order.order_id=t_orderdet.t_order_id)
					JOIN t_bill ON(t_bill.t_order_id=t_order.order_id)
					LEFT JOIN m_customer ON(m_customer.customer_id=t_order.m_customer_id)
				WHERE t_bill.bill_id=".$bill_id;
			
			$result = $this->db->query($sql);
			return $result;
		}

}
?>